﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WXY.Course.Model;

namespace WXY.Course.DAL
{
    public class ResourceDAL
    {
        private static SqlHelper sqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CourseDB"].ConnectionString);

        public static ResourceInfo Get(int code)
        {
            string sql = "select * from TResource where Code=@Code";

            SqlParameter parameter = new SqlParameter("@Code", code);

            return sqlHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                ResourceInfo resource = null;
                while (reader.Read())
                {
                    resource = new ResourceInfo();
                    resource.Code = Convert.ToInt32(reader["Code"]);
                    resource.Title = reader["Title"].ToString();
                    resource.Content = reader["Content"].ToString();
                    resource.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    resource.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    resource.IsRecommended = Convert.ToBoolean(reader["IsRecommended"]);
                    resource.ModuleInfo = ModuleDAL.Get(Convert.ToInt32(reader["ModuleId"]));
                }
                return resource;
            }, parameter);
        }


        public static ResourceInfo GetRecommended()
        {
            string sql = "select * from TResource where IsRecommended=1";

            return sqlHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                ResourceInfo resource = null;
                while (reader.Read())
                {
                    resource = new ResourceInfo();
                    resource.Code = Convert.ToInt32(reader["Code"]);
                    resource.Title = reader["Title"].ToString();
                    resource.Content = reader["Content"].ToString();
                    resource.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    resource.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    resource.IsRecommended = Convert.ToBoolean(reader["IsRecommended"]);
                    resource.ModuleInfo = ModuleDAL.Get(Convert.ToInt32(reader["ModuleId"]));
                }
                return resource;
            }, null);
        }

        public static IList<ResourceInfo> GetList(int moduleId)
        {
            string sql = @"select * from TResource where ModuleId=@ModuleId";

            SqlParameter[] parameter = {
                new SqlParameter("@ModuleId", moduleId)
            };

            return sqlHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                IList<ResourceInfo> list = new List<ResourceInfo>();
                var moduleInfo = ModuleDAL.Get(moduleId);
                while (reader.Read())
                {
                    ResourceInfo resource = new ResourceInfo();
                    resource.Code = Convert.ToInt32(reader["Code"]);
                    resource.Title = reader["Title"].ToString();
                    resource.Content = reader["Content"].ToString();
                    resource.ModuleInfo = moduleInfo;
                    resource.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    resource.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    resource.IsRecommended = Convert.ToBoolean(reader["IsRecommended"]);
                    list.Add(resource);
                }
                return list;
            }, parameter);
        }

        public static IList<ResourceInfo> GetPageList(int moduleId, string title, PageParams pageParams)
        {
            string sql = @"select top (@PageSize) * from (
                            select row_number() over(order by code desc) as rownumber,* from TResource 
                            where (-1 = @ModuleId or ModuleId=@ModuleId)
                            and Title like @Title
                            and (CreateTime between @BeginTime and @EndTime)
                         )
                         t where rownumber > (@PageIndex -1 ) * @PageSize";

            SqlParameter[] parameter = {
                new SqlParameter("@ModuleId", moduleId),
                new SqlParameter("@Title", string.Format("%{0}%",title)),
                new SqlParameter("@BeginTime", pageParams.BeginTime.HasValue ?pageParams.BeginTime.Value: DateTime.Now.AddYears(-100)  ),
                new SqlParameter("@EndTime",  pageParams.EndTime.HasValue ?  pageParams.EndTime.Value :DateTime.Now.AddYears(100)),
                new SqlParameter("@PageIndex",  pageParams.PageIndex),
                new SqlParameter("@PageSize",  pageParams.PageSize),
            };
            pageParams.RecordCount = GetPageCount(moduleId, title, pageParams);
            return sqlHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                IList<ResourceInfo> list = new List<ResourceInfo>();
                while (reader.Read())
                {
                    ResourceInfo resource = new ResourceInfo();

                    resource.Code = Convert.ToInt32(reader["Code"]);
                    resource.Title = reader["Title"].ToString();
                    resource.Content = reader["Content"].ToString();
                    resource.ModuleInfo = ModuleDAL.Get(Convert.ToInt32(reader["ModuleId"]));
                    resource.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    resource.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    resource.IsRecommended = Convert.ToBoolean(reader["IsRecommended"]);
                    list.Add(resource);
                }
                return list;
            }, parameter);
        }

        public static int GetPageCount(int moduleId, string title, PageParams pageParams)
        {
            string sql = @"select count(*) from TResource 
                            where (-1 = @ModuleId or ModuleId=@ModuleId)
                            and Title like @Title
                            and (CreateTime between @BeginTime and @EndTime)";

            SqlParameter[] parameter = {
                new SqlParameter("@ModuleId", moduleId),
                new SqlParameter("@Title", string.Format("%{0}%",title)),
                new SqlParameter("@BeginTime", pageParams.BeginTime.HasValue ?pageParams.BeginTime.Value: DateTime.Now.AddYears(-100)  ),
                new SqlParameter("@EndTime",  pageParams.EndTime.HasValue ?  pageParams.EndTime.Value :DateTime.Now.AddYears(100))
            };
            object o = sqlHelper.ExecuteScalar(sql, CommandType.Text, parameter);

            return Convert.ToInt32(o);
        }

        public static int Delete(int code)
        {
            string sql = @"DELETE FROM TResource WHERE Code=@Code";

            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public static int Update(ResourceInfo info)
        {
            string sql = @"Update TResource
                           set ModuleId=@ModuleId,Title=@Title,Content=@Content,UpdateTime=@UpdateTime
                           where Code=@Code";

            SqlParameter[] parameters ={
                new SqlParameter("@Code", info.Code),
                new SqlParameter("@ModuleId", info.ModuleInfo.Code),
                new SqlParameter("@Title", info.Title),
                new SqlParameter("@Content", info.Content),
                new SqlParameter("@UpdateTime", DateTime.Now)
            };

            return sqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public static int Insert(ResourceInfo info)
        {
            string sql = @"Insert into TResource(ModuleId,UserId,Title,Content,CreateTime,UpdateTime)
                           values (@ModuleId,@UserId,@Title,@Content,@CreateTime,@UpdateTime)";

            SqlParameter[] parameters ={
                new SqlParameter("@ModuleId", info.ModuleInfo.Code),
                new SqlParameter("@UserId", info.UserInfo.Code),
                new SqlParameter("@Title", info.Title),
                new SqlParameter("@Content", info.Content),
                new SqlParameter("@CreateTime", DateTime.Now),
                new SqlParameter("@UpdateTime", DateTime.Now)
            };

            return sqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public static int Recommend(int code)
        {
            string sql = @"if exists(select Code from TResource where Code=@Code)
                                            begin
                                                UPDATE TResource SET IsRecommended=0 WHERE IsRecommended=1 and Code<>@Code    
                                                UPDATE TResource SET IsRecommended=1 WHERE Code=@Code
                                            end";

            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameters);
        }
    }
}
